package com.h3bpm.web.organization.quartz;

import OThinker.Common.Organization.Models.OrganizationUnit;
import OThinker.Common.Organization.Models.Unit;
import OThinker.Common.Organization.Models.User;
import OThinker.Common.Organization.enums.State;
import OThinker.Common.Organization.enums.UserGender;
import OThinker.Common.Organization.enums.VisibleType;
import com.h3bpm.base.engine.client.EngineClient;
import com.h3bpm.base.util.AppUtility;
import com.h3bpm.handler.JSONUtils;
import com.h3bpm.web.common.H3Property;
import com.h3bpm.web.common.utils.SqlUtil;
import com.h3bpm.web.config.UrlConfig;
import com.h3bpm.web.msg.service.TblSmsService;
import com.h3bpm.web.organization.service.PushService;
import com.h3bpm.web.protalHanding.ThreadPost;
import data.DataRow;
import data.DataTable;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author villwang
 * @date 2020/08/12
 **/
@Component
public class PushQuartz {
    private static final Logger logger = LoggerFactory.getLogger(PushQuartz.class);

    @Autowired
    PushService pushService;

    @Value("${task.bpm}")
    public String taskBpm;

    @Autowired
    private TblSmsService tblSmsService;


    public User getAdministrator() throws Exception {
        User user = getEngine().getOrganization().GetUserByCode("Administrator");
        return user;
    }
    private static volatile EngineClient Engine = null;
    private static volatile boolean shouldRefreshEngine = false;
    public static EngineClient getEngine() {
        if (Engine == null || shouldRefreshEngine) {
            try {
                Engine = (EngineClient) AppUtility.getEngine();
            } catch (Exception e) {
                logger.error("get engine fail", e);
                throw new RuntimeException(e);
            }
            shouldRefreshEngine = false;
        }
        return Engine;
    }
    /**
     * 每日数据统计
     */
//	 生产服务器BPM1定时任务:
	 @Scheduled(cron = "13 20 0 * * ?")
//	 生产服务器BPM2定时任务:
//	 @Scheduled(cron = "13 20 1 * * ?")
    public void updateEveningPush() {
	     if("0".equals(taskBpm)){
	         return;
         }
        logger.info("每日午夜人事推送删除历史数据开始,{}");
        pushService.delDbo();
        logger.info("每日午夜人事推送删除历史数据结束,{}");

        logger.info("每日午夜短信推送删除历史数据开始,{}");
        tblSmsService.clearSms();
        logger.info("每日午夜短信推送删除历史数据结束,{}");
    }

    //	 生产服务器BPM1定时任务:
//    @Scheduled(cron = "13 20 0 * * ?")
//	 生产服务器BPM2定时任务:
	 @Scheduled(cron = "13 20 1 * * ?")
    public void updateEveningPush2() {
         if("1".equals(taskBpm)){
             return;
         }
        logger.info("每日午夜人事推送删除历史数据开始,{}");
        pushService.delDbo();
        logger.info("每日午夜人事推送删除历史数据结束,{}");

        logger.info("每日午夜短信推送删除历史数据开始,{}");
        tblSmsService.clearSms();
        logger.info("每日午夜短信推送删除历史数据结束,{}");
    }
    /**
     * 定时处理推送数据
     */
//	 生产服务器BPM1定时任务:
	 @Scheduled(cron = "45 */5 * * * ?")
//	 生产服务器BPM2定时任务:
//    @Scheduled(cron = "16 */4 * * * ?")
    public void updateEvening() {
         if("0".equals(taskBpm)){
             return;
         }
        long start = System.currentTimeMillis();
        logger.info("组织机构用户信息推送，开始处理-------："+ taskBpm+start);
        //处理ETL中间表全量比较修改的组织机构和用户
        List<HashMap<String, Object>> dboUnits = pushService.getUnprocessedDboUnit();
        for (HashMap<String, Object> dboUnit : dboUnits) {
            logger.info("处理ETL中间表全量比较修改的组织信息："+dboUnit);
            organizationSyncUnit(dboUnit, 0, true);
        }
        List<HashMap<String, Object>> dboUsers = pushService.getUnprocessedDboUser();
        for (HashMap<String, Object> dboUser : dboUsers) {
            logger.info("处理ETL中间表全量比较修改的用户信息："+dboUser);
            organizationSyncUser(dboUser, 0, true);
        }
        List<HashMap<String, Object>> dbo = pushService.getDbo();
        if(dbo.isEmpty()){}else {
            String json="";
            String flag="";
            int id=0;
            try {
                getEngine().getOrganization().Reload();
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (HashMap<String, Object> map : dbo) {
                json= (String) map.get("json");
                flag=(String)map.get("flag");
                id= (int) map.get("id");
                Map<String, Object> stringObjectMap = null;
                try {
                    stringObjectMap = JSONUtils.parseMap(json);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                if("2".equals(flag)){
                    organizationSyncUser(stringObjectMap,id, false);
                }
                if("1".equals(flag)){
                    organizationSyncUnit(stringObjectMap,id, false);
                }
                pushService.updatDbo(id);
            }
        }
        try {
            getEngine().getOrganization().Reload();
        } catch (Exception e) {
            e.printStackTrace();
        }
        int i = dbo.size()+dboUnits.size()+dboUsers.size();
        logger.info("组织机构用户信息推送，结束处理 ：" +(System.currentTimeMillis() - start) + "ms，数据量："+i);
    }

    /**
     * 定时处理推送数据
     */
//	 生产服务器BPM1定时任务:
    //   @Scheduled(cron = "45 */5 * * * ?")
//	 生产服务器BPM2定时任务:
    @Scheduled(cron = "16 */4 * * * ?")
    public void updateEvening2() {
        if("1".equals(taskBpm)){
            return;
        }
        long start = System.currentTimeMillis();
        logger.info("组织机构用户信息推送，开始处理-------："+ taskBpm+ start);
        //处理ETL中间表全量比较修改的组织机构和用户
        List<HashMap<String, Object>> dboUnits = pushService.getUnprocessedDboUnit();
        for (HashMap<String, Object> dboUnit : dboUnits) {
            logger.info("处理ETL中间表全量比较修改的组织信息："+dboUnit);
            organizationSyncUnit(dboUnit, 0, true);
        }
        List<HashMap<String, Object>> dboUsers = pushService.getUnprocessedDboUser();
        for (HashMap<String, Object> dboUser : dboUsers) {
            logger.info("处理ETL中间表全量比较修改的用户信息："+dboUser);
            organizationSyncUser(dboUser, 0, true);
        }
        List<HashMap<String, Object>> dbo = pushService.getDbo();
        if(dbo.isEmpty()){}else {
            String json="";
            String flag="";
            int id=0;
            try {
                getEngine().getOrganization().Reload();
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (HashMap<String, Object> map : dbo) {
                json= (String) map.get("json");
                flag=(String)map.get("flag");
                id= (int) map.get("id");
                Map<String, Object> stringObjectMap = null;
                try {
                    stringObjectMap = JSONUtils.parseMap(json);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                if("2".equals(flag)){
                    organizationSyncUser(stringObjectMap,id, false);
                }
                if("1".equals(flag)){
                    organizationSyncUnit(stringObjectMap,id, false);
                }
                pushService.updatDbo(id);
            }
        }
        try {
            getEngine().getOrganization().Reload();
        } catch (Exception e) {
            e.printStackTrace();
        }
        int i = dbo.size()+dboUnits.size()+dboUsers.size();
        logger.info("组织机构用户信息推送，结束处理 ：" +(System.currentTimeMillis() - start) + "ms，数据量："+i);
    }
    public void organizationSyncUser(Map<String, Object> stringObjectMap,int id, boolean isEtl) {
        String flag= (String) stringObjectMap.get("flag");
        Map<String,String> data = new HashMap<>();
        data= (Map<String, String>) stringObjectMap.get("data");
        logger.info("人员数据回写接口-——————flag——"+flag);
        int i = 1;
        if(StringUtils.isNotBlank(flag)){
            if(data==null||data.equals("")||data.isEmpty()){
            }else {
                //新增
                if(flag.equals("1")){
                    logger.info("人员数据回写接口————————新增");
                    i =addUser(data, isEtl);
                    //删除
                }else if(flag.equals("2")){
                    logger.info("人员数据回写接口————————删除");
                    i=delectUser(data);
                }else {
                    //修改
                    updatePersonnelDeptByJobNo(data);
                    logger.info("人员数据回写接口————————修改");
                    i= updateUser(data, isEtl);
                }
                pushService.updatDboUserStatus(data.get("emp_idcode"));
            }
        }
    }
    public void organizationSyncUnit(Map<String, Object> stringObjectMap,int id, boolean isEtl){
        String flag= (String) stringObjectMap.get("flag");
        Map<String,String> data = new HashMap<>();
        data= (Map<String, String>) stringObjectMap.get("data");
        int i = 0;
        if(StringUtils.isNotBlank(flag)){
            if(data==null||data.equals("")||data.isEmpty()){

            }else {
                //新增
                if(flag.equals("1")){
                    logger.info("组织数据回写接口————————新增");
                    i =addUnit(data, isEtl);
                    //删除
                }else if(flag.equals("2")){
                    logger.info("组织数据回写接口————————删除");
                    i=delectUnit(data);
                }else {
                    //修改
                    updatePersonnelDept(data);
                    logger.info("组织数据回写接口————————修改");
                    i= updateUnit(data, isEtl);
                }
                pushService.updatDboUnitStatus(data.get("dept_idcode"));
            }
        }

    }

    /**
     * 修改部门名称通过用户编号
     * @param data
     */
    public void updatePersonnelDeptByJobNo(Map<String,String> data){
        String newDeptIdcode  = data.get("dept_idcode");
        String empNumber = data.get("emp_number");
        if(StringUtils.isBlank(newDeptIdcode)||StringUtils.isBlank(empNumber)){
            return;
        }
        String sql="select dept_idcode from dbo_user where emp_number='"+empNumber+"'";
        String newDeptContentSql="select dept_content from dbo_unit where dept_idcode='"+newDeptIdcode+"'";
        //判断部门名称是否修改
        DataTable dataTable = null;
        HashMap<String, Object> map = new HashMap<>();
        try {
            dataTable = SqlUtil.doQuery(sql);
            if (!CollectionUtils.isEmpty(dataTable.getEntityRows())) {
                String oldDeptIdcode = dataTable.getEntityRows().get(0).getString("dept_idcode");
                if (StringUtils.isNotBlank(oldDeptIdcode) && StringUtils.isNotBlank(newDeptIdcode) && !newDeptIdcode.equals(oldDeptIdcode)) {
                    //调用接口
                    DataTable newDeptDataTable = SqlUtil.doQuery(newDeptContentSql);
                    if (!CollectionUtils.isEmpty(newDeptDataTable.getEntityRows())) {
                        String deptContent = newDeptDataTable.getEntityRows().get(0).getString("dept_content");
                        if(StringUtils.isNotBlank(deptContent)){
                            map.put("newPersonnelDept",deptContent);
                            map.put("jobNo", empNumber);
                            String json = JSONUtils.toJson(map);
                            String postUrl = UrlConfig.BusinessUrl+"/prod-api/workflow/updatePersonnelDeptByJobNo";
                            Map<String, Object> result = ThreadPost.postRequestResult(postUrl,json,null);
                            if(result!=null&&result.size()!=0&&(Integer) result.get("code")==200){
                                logger.debug("调用接口updatePersonnelDeptByJobNo====================success");
                            }else{
                                logger.debug("调用接口updatePersonnelDeptByJobNo====================fail");
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改部门名称
     * @param data
     */
    public void updatePersonnelDept(Map<String,String> data){
        String newPersonnelDept  = data.get("dept_content");
        if(StringUtils.isBlank(newPersonnelDept)||StringUtils.isBlank(data.get("dept_idcode"))){
            return;
        }
        //判断名称是否修改
        String sql="select dept_content FROM dbo_unit WHERE dept_idcode = '"+data.get("dept_idcode")+"'";
        DataTable dataTable = null;
        HashMap<String, Object> map = new HashMap<>();
        try {
            dataTable = SqlUtil.doQuery(sql);
            if (!CollectionUtils.isEmpty(dataTable.getEntityRows())) {
                String oldDeptContent = dataTable.getEntityRows().get(0).getString("dept_content");
                if(StringUtils.isNotBlank(oldDeptContent)&&StringUtils.isNotBlank(newPersonnelDept)&&!newPersonnelDept.equals(oldDeptContent)){
                    //调用接口
                    map.put("oldPersonnelDept",oldDeptContent);
                    map.put("newPersonnelDept",data.get("dept_content"));
                    String json = JSONUtils.toJson(map);
                    String postUrl = UrlConfig.BusinessUrl+"/prod-api/workflow/updatePersonnelDept";
                    Map<String, Object> result = ThreadPost.postRequestResult(postUrl,json,null);
                    if(result!=null&&result.size()!=0&&(Integer) result.get("code")==200){
                        logger.debug("调用接口updatePersonnelDept====================success");
                    }else{
                        logger.debug("调用接口updatePersonnelDept====================fail");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private int delectUnit(Map<String, String> data) {
        try {
            Unit unit = getUnitByDeptId(data.get("dept_id"));
            if (null != unit) {
                unit.setState(State.Inactive);
                getEngine().getOrganization().RemoveUnit(getAdministrator().getObjectID(), unit);
                //getEngine().getOrganization().UpdateUnit(getAdministrator().getObjectID(), unit);
                //删除dbo_user数据
                String sql="DELETE FROM dbo_unit WHERE dept_id = '"+data.get("dept_id")+"'";
                int i = SqlUtil.doNoQuery(sql);
                return i;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    private int updateUnit(Map<String, String> data, boolean isEtl) {
        try {
            Unit unit = getUnitByDeptId(data.get("dept_id"));
            if (null != unit) {
                unit.setName(data.get("dept_content"));//名称
//                unit.setSortKey(0);//排序值
                if (data.get("dept_deleted").equals("1")) {
                    unit.setState(State.Inactive);
                } else {
                    unit.setState(State.Active);
                }
                //Inactive(0),禁用 Active(1),启用 Unspecified(-1);
                unit.setVisibility(VisibleType.Normal); //Normal(1),普通，同AllOnlyAdmin(2),管理员可见OnlyOrganization(4),仅本组织范围可见All(5);
                unit.setParentIndex(0);
                unit.setDescription(data.get("dept_id"));
                unit.setExtend1(data.get("dept_code"));//组织iD
                unit.setExtend2(data.get("dept_parentcode"));//所属组织 上级
                String parentSql= " select objectid,Description from ot_organizationunit where Description = ";
                DataTable parentUnit = SqlUtil.doQuery(parentSql+"'"+data.get("dept_parentid")+"'");
                if(parentUnit!=null && parentUnit.getEntityRows().size()>0 && !"".equals(data.get("dept_parentid"))) {
                    unit.setParentID(parentUnit.getEntityRows().get(0).getString("objectid"));
                }
                if( "ROOT".equals(data.get("dept_parentcode"))) {
                    unit.setParentID(getEngine().getOrganization().getRootUnit().getObjectID());
                }
                getEngine().getOrganization().UpdateUnit(getAdministrator().getObjectID(), unit);
                //修改dbo_unit
                String sql="DELETE FROM dbo_unit WHERE dept_id = '"+data.get("dept_id")+"'";
                int i = SqlUtil.doNoQuery(sql);

                String sql2="INSERT INTO dbo_unit (dept_id,dept_code,dept_content,dept_grade,dept_parentcode,dept_inchargeleader,\n" +
                        "dept_leader,dept_oaorgcode,dept_idcode,dept_ordercode,dept_parentidcode,dept_deleted,dept_type,\n" +
                        "dept_allcontent,dept_endmark,dept_inchargeleaderid,dept_leaderid,dept_parentid," +
                        "dept_instituteleader,dept_leader_approve,dept_instituteleader_approve,dept_inchargeleader_approve,dept_leaderid_approve,dept_instituteleaderid_approve,dept_inchargeleaderid_approve,dept_processtype,dept_instituteleaderid,status)\n" +
                        "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
                sql2=String.format(sql2,data.get("dept_id"),data.get("dept_code"),data.get("dept_content"),
                        data.get("dept_grade"),data.get("dept_parentcode"),
                        data.get("dept_inchargeleader"),data.get("dept_leader"),data.get("dept_oaorgcode"),
                        data.get("dept_idcode"),data.get("dept_ordercode")
                        ,data.get("dept_parentidcode"),data.get("dept_deleted"),data.get("dept_type"),data.get("dept_allcontent"),
                        data.get("dept_endmark"),data.get("dept_inchargeleaderid"),data.get("dept_leaderid"),data.get("dept_parentid"),data.get("dept_instituteleader"),
                        data.get("dept_leader_approve"),
                        data.get("dept_instituteleader_approve"),
                        data.get("dept_inchargeleader_approve"),
                        data.get("dept_leaderid_approve"),
                        data.get("dept_instituteleaderid_approve"),
                        data.get("dept_inchargeleaderid_approve"),
                        data.get("dept_processtype"),
                        data.get("dept_instituteleaderid"),"1");
                SqlUtil.doNoQuery(sql2);
            }else{
                addUnit(data, isEtl);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    private int addUnit(Map<String, String> data, boolean isEtl) {
        try {
            logger.info("组织数据回写接口-————————dept_id---"+data.get("dept_id"));
            String getH3UnSql= " select objectid,extend1 from ot_organizationunit  where description = ";
            DataTable isUnit = SqlUtil.doQuery(getH3UnSql + "'" + data.get("dept_id") + "'");
            logger.info("组织数据回写接口-————————h3User---"+isUnit);
            if (!(isUnit.getEntityRows().size() > 0) || isUnit == null) {
                OrganizationUnit unit = new OrganizationUnit();
                unit.setName(data.get("dept_content"));//名称
                unit.setIsRootUnit(false);//是否是根节点
                unit.setSortKey(0);//排序值
                if (data.get("dept_deleted").equals("1")) {
                    unit.setState(State.Inactive);
                } else {
                    unit.setState(State.Active);
                }
                //Inactive(0),禁用 Active(1),启用 Unspecified(-1);
                unit.setVisibility(VisibleType.Normal); //Normal(1),普通，同AllOnlyAdmin(2),管理员可见OnlyOrganization(4),仅本组织范围可见All(5);
                unit.setParentIndex(0);
                unit.setDescription(data.get("dept_id"));
                unit.setExtend1(data.get("dept_code"));//组织iD
                unit.setExtend2(data.get("dept_parentcode"));//所属组织 上级
                DataTable parentUnit = SqlUtil.doQuery(getH3UnSql+"'"+data.get("dept_parentid")+"'");
                if(parentUnit!=null && parentUnit.getEntityRows().size()>0 && !"".equals(data.get("dept_parentcode"))) {
                    unit.setParentID(parentUnit.getEntityRows().get(0).getString("objectid"));
                }
                if( "ROOT".equals(data.get("dept_parentcode"))) {
                    unit.setParentID(getEngine().getOrganization().getRootUnit().getObjectID());
                }
                //创建组织
                getEngine().getOrganization().AddUnit(getAdministrator().getObjectID(), unit);
                if(!isEtl){
                    String sql2="INSERT INTO dbo_unit (dept_id,dept_code,dept_content,dept_grade,dept_parentcode,dept_inchargeleader,\n" +
                            "dept_leader,dept_oaorgcode,dept_idcode,dept_ordercode,dept_parentidcode,dept_deleted,dept_type,\n" +
                            "dept_allcontent,dept_endmark,dept_inchargeleaderid,dept_leaderid,dept_parentid,status)\n" +
                            "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
                    sql2=String.format(sql2,data.get("dept_id"),data.get("dept_code"),data.get("dept_content"),
                            data.get("dept_grade"),data.get("dept_parentcode"),
                            data.get("dept_inchargeleader"),data.get("dept_leader"),data.get("dept_oaorgcode"),
                            data.get("dept_idcode"),data.get("dept_ordercode")
                            ,data.get("dept_parentidcode"),data.get("dept_deleted"),data.get("dept_type"),data.get("dept_allcontent"),
                            data.get("dept_endmark"),data.get("dept_inchargeleaderid"),data.get("dept_leaderid"),data.get("dept_parentid"),"1");
                    SqlUtil.doNoQuery(sql2);
                }else{
                    String sql="update dbo_unit set status='1',flag=null where dept_id='"+data.get("dept_id")+"'";
                    SqlUtil.doNoQuery(sql);
                }
            }else{
                updateUnit(data, isEtl);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    //更新
    private int updateUser(Map<String, String> map,boolean isEtl) {
        try {

            Boolean flag=false;
            String emp_idcode=map.get("emp_idcode");
            String emp_id=map.get("emp_id");
            User h3User = getUserByEmpId(emp_id);
            SimpleDateFormat fm= new SimpleDateFormat("yyyy-MM-dd");
            logger.info("人员数据回写接口-————————emp_number---"+map.get("emp_number"));
            String getH3UnSql= " select objectid,description from ot_organizationunit  where description = ";
            if(null != h3User) {
                String deptno = "";
                String dept_id = map.get("dept_id");
                if (dept_id == null || "".equals(dept_id)) {
                    deptno = getEngine().getOrganization().getRootUnit().getObjectID();
                } else {
                    deptno = dept_id;
                }
                DataTable unitTable = SqlUtil.doQuery(getH3UnSql + "'" + deptno + "'");
                if (unitTable.getEntityRows().size() > 0) {
                    h3User.setParentID(unitTable.getEntityRows().get(0).getString("objectid"));
                }
                //修改时不改变用户状态
//                h3User.setState(State.Active);
                //h3User.setEmployeeNumber(map.get("employeeno").toString()); 编号
                String emp_sex = map.get("emp_sex");
                if (map.get("emp_sex") != null && "0".equals(map.get("emp_sex"))) {
                    h3User.setGender(UserGender.Male);
                } else {
                    h3User.setGender(UserGender.Female);
                }
                String emp_entrydate = map.get("emp_entrydate");
                if (map.get("emp_entrydate") != null && !"".equals(map.get("emp_entrydate"))) {
                    h3User.setEntryDate(fm.parse((map.get("emp_entrydate"))));
                }
                String emp_quitdate = map.get("emp_quitdate");
                if (map.get("emp_quitdate") != null && !"".equals(map.get("emp_quitdate"))) {
                    h3User.setDepartureDate(fm.parse(map.get("emp_quitdate")));
                }
                String codeBpm=h3User.getCode();
                String emp_number = map.get("emp_number");
                //code相同的不用处理
                if(emp_number.equals(codeBpm)){
                    // h3User.setCode(map.get("emp_number"));
                }else {
                    // 不同的数据需要特殊处理
                    flag=true;
                }
                String emp_name = map.get("emp_name");
                h3User.setName(map.get("emp_name"));
                String emp_mobilephone = map.get("emp_mobilephone");
                if (map.get("emp_mobilephone") != null) {
                    h3User.setMobile(map.get("emp_mobilephone"));
                }
                h3User.setExtend1(map.get("emp_id"));
                getEngine().getOrganization().UpdateUnit(getAdministrator().getObjectID(), h3User);
                if(flag){
                    //需要强制修改user code
                    String user="update ot_user set code='"+emp_number+"' where objectId='"+h3User.getObjectID()+"'";
                    SqlUtil.doNoQuery(user);
                }
                //修改dbo_user
                logger.info("人员数据回写接口-————————更新OT_user成功");
                //删除dbo_user数据
                String sql="DELETE FROM dbo_user WHERE emp_id = '"+map.get("emp_id")+"'";
                int i = SqlUtil.doNoQuery(sql);
                logger.info("人员数据回写接口-————————删除dbo");
                //新增dbo_user数据 String.format
                String sql2="INSERT INTO dbo_user (emp_id,emp_idcode,emp_number,emp_name,dept_id,dept_idcode,emp_idnumber,emp_mobilephone,\n" +
                        "emp_projectleader,emp_projectpayee,emp_judgeprojectleader,emp_judgeprojectpayee,emp_incumbencystate,emp_entrydate,\n" +
                        "emp_quitdate,emp_projectleaderid,emp_projectpayeeid,emp_sex,part_type,idtype_content,competentorg_content,\n" +
                        "incumbency_content,emp_numbereffectiveness,empisvalid_content,emp_datavalid,emp_companyemail,emp_personneltype,\n" +
                        "persontype_content,status) \n" +
                        "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','','%s','%s','%s','%s','%s','%s',\n" +
                        "'%s','%s','%s','%s','%s','%s','%s','%s')";
                String dept_idcode=map.get("dept_idcode")!=null?map.get("dept_idcode"):"";
                String emp_idnumber=map.get("emp_idnumber")!=null?map.get("emp_idnumber"):"";
                String emp_projectleader=map.get("emp_projectleader")!=null?map.get("emp_projectleader"):"";
                String emp_projectpayee=map.get("emp_projectpayee")!=null?map.get("emp_projectpayee"):"";
                String emp_judgeprojectleader=map.get("emp_judgeprojectleader")!=null?map.get("emp_judgeprojectleader"):"";
                String emp_judgeprojectpayee=map.get("emp_judgeprojectpayee")!=null?map.get("emp_judgeprojectpayee"):"";
                String emp_incumbencystate=map.get("emp_incumbencystate")!=null?map.get("emp_incumbencystate"):"";
                String emp_projectleaderid=map.get("emp_projectleaderid")!=null?map.get("emp_projectleaderid"):"";
                String emp_projectpayeeid=map.get("emp_projectpayeeid")!=null?map.get("emp_projectpayeeid"):"";
                String part_type=map.get("part_type")!=null?map.get("part_type"):"";//主职
                String idtype_content=map.get("idtype_content")!=null?map.get("idtype_content"):"";//证件类型
                String competentorg_content=map.get("competentorg_content")!=null?map.get("competentorg_content"):"";//主管单位名称
                String incumbency_content=map.get("incumbency_content")!=null?map.get("incumbency_content"):"";//在职状态
                String emp_numbereffectiveness=map.get("emp_numbereffectiveness")!=null?map.get("emp_numbereffectiveness"):"";//员工编号有效性
                String empisvalid_content=map.get("empisvalid_content")!=null?map.get("empisvalid_content"):"";//员工编号有效性
                String emp_datavalid=map.get("emp_datavalid")!=null?map.get("emp_datavalid"):"";//人事资料有效期
                String emp_companyemail=map.get("emp_companyemail")!=null?map.get("emp_companyemail"):"";//公司邮箱
                String emp_personneltype=map.get("emp_personneltype")!=null?map.get("emp_personneltype"):"";//人员类别ID
                String persontype_content=map.get("persontype_content")!=null?map.get("persontype_content"):"";//人员类别
                sql2=String.format(sql2,emp_id,emp_idcode,emp_number,emp_name,dept_id,
                        dept_idcode,emp_idnumber,emp_mobilephone,emp_projectleader,emp_projectpayee
                        ,emp_judgeprojectleader,emp_judgeprojectpayee,emp_incumbencystate,emp_entrydate,
                        emp_projectleaderid,emp_projectpayeeid,emp_sex,part_type,idtype_content,
                        competentorg_content,incumbency_content,emp_numbereffectiveness,empisvalid_content,
                        emp_datavalid,emp_companyemail,emp_personneltype,persontype_content,"1");
                i= SqlUtil.doNoQuery(sql2);
            }else{
                addUser(map, isEtl);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  1;
    }
    //删除
    private int delectUser(Map<String, String> map) {
        try {
            User h3User = getUserByEmpId(map.get("emp_id"));
            if(null != h3User) {
                String code=map.get("emp_number");
                if(code.contains("H04")){
                    h3User.setState(State.Inactive);
                    getEngine().getOrganization().UpdateUnit(getAdministrator().getObjectID(), h3User);
                }else {
                    String sqlDel="DELETE FROM ot_user WHERE objectID = '"+h3User.getObjectID()+"'";
                    SqlUtil.doNoQuery(sqlDel);
                    //删除dbo_user数据
                    String sql="DELETE FROM dbo_user WHERE emp_id = '"+map.get("emp_id")+"'";
                    int i = SqlUtil.doNoQuery(sql);
                    return i;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  0;
    }

    /**
     *
     *@describe  新增
     *@params [data]
     *@return int
     *@author VillWang
     *@date 2020/6/19
     *@other
     *
     */
    public int addUser(Map<String,String> map, boolean isEtl){
        try {
            String getH3UnSql= " select objectid,description from ot_organizationunit  where description = ";
            SimpleDateFormat fm= new SimpleDateFormat("yyyy-MM-dd");
            //如果人员不存在则新增此人员
            String emp_idcode=map.get("emp_idcode");
            String emp_id=map.get("emp_id");
            User h3User=new User();
            String sql2 = "SELECT objectid,extend1 FROM ot_user WHERE extend1='"+emp_id+"'";
            DataTable dataTable = SqlUtil.doQuery(sql2);
            List<DataRow> entityRows = dataTable.getEntityRows();
            if(!entityRows.isEmpty()&&entityRows.size()>0){
                logger.info("人员数据回写接口-————————已存在，做更新处理");
                updateUser(map, isEtl);
            }else {
                String deptno ="";
                String dept_id =  map.get("dept_id");
                if(dept_id==null || "".equals(dept_id)){
                    deptno=getEngine().getOrganization().getRootUnit().getObjectID();
                }else{
                    deptno = dept_id;
                }
                DataTable unitTable = SqlUtil.doQuery(getH3UnSql+"'"+deptno+"'");
                if( unitTable.getEntityRows().size()>0){
                    h3User.setParentID(unitTable.getEntityRows().get(0).getString("objectid"));
                }
                //新增时用户状态禁用，ec新增用户调用接口触发BPM用户启用
                h3User.setState(State.Inactive);
//                h3User.setState(State.Active);
                //h3User.setEmployeeNumber(map.get("employeeno").toString()); 编号
                String emp_sex= map.get("emp_sex");
                if(map.get("emp_sex")!=null && "0".equals(map.get("emp_sex"))) {
                    h3User.setGender(UserGender.Male);
                }else{
                    h3User.setGender(UserGender.Female);
                }
                String emp_entrydate= map.get("emp_entrydate");
                logger.info("emp_entrydate : "+map.get("emp_entrydate"));
                if(map.get("emp_entrydate")!=null && !"".equals(map.get("emp_entrydate"))){
                    h3User.setEntryDate(fm.parse((map.get("emp_entrydate"))));
                }
                String emp_quitdate= map.get("emp_quitdate");
                if(map.get("emp_quitdate")!=null && !"".equals(map.get("emp_quitdate"))) {
                    h3User.setDepartureDate(fm.parse(map.get("emp_quitdate")));
                }
                String emp_number= map.get("emp_number");
                h3User.setCode(map.get("emp_number"));
                String emp_name= map.get("emp_name");
                h3User.setName(map.get("emp_name"));
                String emp_mobilephone= map.get("emp_mobilephone");
                if(map.get("emp_mobilephone")!=null) {
                    h3User.setMobile(map.get("emp_mobilephone"));
                }
                h3User.setExtend1(map.get("emp_id"));
                h3User.setPassword(H3Property.DEFAULT_PASSWORD);//新增用户默认密码为：Tjec00000000
                getEngine().getOrganization().AddUnit(getAdministrator().getObjectID(), h3User);
                //ETl同步不更新
                if(!isEtl){
                    //新增dbo_user数据 String.format
                    String sql="INSERT INTO dbo_user (emp_id,emp_idcode,emp_number,emp_name,dept_id,dept_idcode,emp_idnumber,emp_mobilephone,\n" +
                            "emp_projectleader,emp_projectpayee,emp_judgeprojectleader,emp_judgeprojectpayee,emp_incumbencystate,emp_entrydate,\n" +
                            "emp_quitdate,emp_projectleaderid,emp_projectpayeeid,emp_sex,part_type,idtype_content,competentorg_content,\n" +
                            "incumbency_content,emp_numbereffectiveness,empisvalid_content,emp_datavalid,emp_companyemail,emp_personneltype,\n" +
                            "persontype_content,status) \n" +
                            "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','','%s','%s','%s','%s','%s','%s',\n" +
                            "'%s','%s','%s','%s','%s','%s','%s','%s')";
                    //emp_idcode=map.get("emp_idcode");
                    String dept_idcode=map.get("dept_idcode")!=null?map.get("dept_idcode"):"";
                    String emp_idnumber=map.get("emp_idnumber")!=null?map.get("emp_idnumber"):"";
                    String emp_projectleader=map.get("emp_projectleader")!=null?map.get("emp_projectleader"):"";
                    String emp_projectpayee=map.get("emp_projectpayee")!=null?map.get("emp_projectpayee"):"";
                    String emp_judgeprojectleader=map.get("emp_judgeprojectleader")!=null?map.get("emp_judgeprojectleader"):"";
                    String emp_judgeprojectpayee=map.get("emp_judgeprojectpayee")!=null?map.get("emp_judgeprojectpayee"):"";
                    String emp_incumbencystate=map.get("emp_incumbencystate")!=null?map.get("emp_incumbencystate"):"";
                    String emp_projectleaderid=map.get("emp_projectleaderid")!=null?map.get("emp_projectleaderid"):"";
                    String emp_projectpayeeid=map.get("emp_projectpayeeid")!=null?map.get("emp_projectpayeeid"):"";
                    String part_type=map.get("part_type")!=null?map.get("part_type"):"";//主职
                    String idtype_content=map.get("idtype_content")!=null?map.get("idtype_content"):"";//证件类型
                    String competentorg_content=map.get("competentorg_content")!=null?map.get("competentorg_content"):"";//主管单位名称
                    String incumbency_content=map.get("incumbency_content")!=null?map.get("incumbency_content"):"";//在职状态
                    String emp_numbereffectiveness=map.get("emp_numbereffectiveness")!=null?map.get("emp_numbereffectiveness"):"";//员工编号有效性
                    String empisvalid_content=map.get("empisvalid_content")!=null?map.get("empisvalid_content"):"";//员工编号有效性
                    String emp_datavalid=map.get("emp_datavalid")!=null?map.get("emp_datavalid"):"";//人事资料有效期
                    String emp_companyemail=map.get("emp_companyemail")!=null?map.get("emp_companyemail"):"";//公司邮箱
                    String emp_personneltype=map.get("emp_personneltype")!=null?map.get("emp_personneltype"):"";//人员类别ID
                    String persontype_content=map.get("persontype_content")!=null?map.get("persontype_content"):"";//人员类别
                    sql=String.format(sql,emp_id,emp_idcode,emp_number,emp_name,dept_id,
                            dept_idcode,emp_idnumber,emp_mobilephone,emp_projectleader,emp_projectpayee
                            ,emp_judgeprojectleader,emp_judgeprojectpayee,emp_incumbencystate,emp_entrydate,
                            emp_projectleaderid,emp_projectpayeeid,emp_sex,part_type,idtype_content,
                            competentorg_content,incumbency_content,emp_numbereffectiveness,empisvalid_content,
                            emp_datavalid,emp_companyemail,emp_personneltype,persontype_content,"1");
                    SqlUtil.doNoQuery(sql);
                }else{
                    String sql="update dbo_user set status='1',flag=null where emp_id='"+emp_id+"'";
                    SqlUtil.doNoQuery(sql);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }
    
    
    /**
     * 项目负责人用户组用户同步
     */
    //生产服务器BPM1定时任务:
	@Scheduled(cron = "45 */4 * * * ?")
	//生产服务器BPM2定时任务:
	//@Scheduled(cron = "16 */4 * * * ?")
    public void syncProjectLeaderGroupUser() {
		logger.info("------------------开始项目负责人用户组用户同步------------------");
		try {
			//需要删除出组的
			String deleteGroupUser = "select childId from ot_groupchild where parentObjectId='87c01d95-b281-4fd5-9293-50f4ef33f404' and childId not in (select ou.objectId from (select objectId,code from ot_user where state='1') ou INNER JOIN (select emp_number from dbo_user where emp_judgeprojectleader='1') du on du.emp_number=ou.code)";
			DataTable deleteUsers = SqlUtil.doQuery(deleteGroupUser);
			if(deleteUsers.getEntityRows().size()>0) {
				List<DataRow> rows = deleteUsers.getEntityRows();
				for (DataRow dataRow : rows) {
					String objectId = dataRow.getString("childId");
					logger.info("新删除组人员objectId=="+objectId);
					String deleteSql = "delete from ot_groupchild where ChildID='"+objectId+"'";
					int i = SqlUtil.doNoQuery(deleteSql);
					logger.info("新删除组人员结果=="+i);
				}
			}
			
			//需要新加进组的
			String insertGroupUser = "select ou.objectId from (select objectId,code from ot_user where state='1') ou INNER JOIN (select emp_number from dbo_user where emp_judgeprojectleader='1') du on du.emp_number=ou.code where ou.objectId not in (select childId from ot_groupchild where parentObjectId='87c01d95-b281-4fd5-9293-50f4ef33f404')";
			DataTable insertUsers = SqlUtil.doQuery(insertGroupUser);
			if(insertUsers.getEntityRows().size()>0) {
				List<DataRow> rows = insertUsers.getEntityRows();
				for (DataRow dataRow : rows) {
					String objectId = dataRow.getString("objectId");
					logger.info("新进组人员objectId=="+objectId);
					String insertSql = "insert into ot_groupchild (ObjectID,ChildID,IsMain,ParentIndex,ParentPropertyName,ParentObjectID) value(UUID(),'"+objectId+"',0,0,'ChildList','87c01d95-b281-4fd5-9293-50f4ef33f404')";
					int i = SqlUtil.doNoQuery(insertSql);
					logger.info("新进组人员结果=="+i);
				}
			}
			logger.info("------------------结束项目负责人用户组用户同步------------------");
			getEngine().getOrganization().Reload();
		} catch (Exception e) {
			e.printStackTrace();
		}
    }

    /**
     * 通过emp_id查询用户是否存在，如果存在，返回该用户
     * @param empId
     * @return
     */
    private User getUserByEmpId(String empId) throws Exception {
        if(StringUtils.isBlank(empId)){
            return null;
        }
        User h3User = null;
        String userSql = "SELECT objectid,extend1 FROM ot_user WHERE extend1='"+empId+"'";
        DataTable dataTable = SqlUtil.doQuery(userSql);
        List<DataRow> entityRows = dataTable.getEntityRows();
        if(!entityRows.isEmpty()&&entityRows.size()>0) {
            h3User = (User) getEngine().getOrganization().GetUnit(entityRows.get(0).getString("objectid"));
        }
        return h3User;
    }

    /**
     * 通过deptId查询组织是否存在，如果存在，返回该组织
     * @param deptId
     * @return
     */
    private Unit getUnitByDeptId(String deptId) throws Exception {
        if(StringUtils.isBlank(deptId)){
            return null;
        }
        Unit unit = null;
        String getH3UnSql= " select objectid,description from ot_organizationunit  where description = ";
        DataTable thisUnit = SqlUtil.doQuery(getH3UnSql + "'" + deptId + "'");
        if (thisUnit != null && thisUnit.getEntityRows().size() > 0) {
            unit = getEngine().getOrganization().GetUnit(thisUnit.getEntityRows().get(0).getString("objectid"));
        }
        return unit;
    }


}
